/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package orest.common.dao;

import java.sql.*;
import orest.common.response.*;
import orest.common.util.DataBase;
import orest.common.util.Tools;
import java.util.ArrayList;
import orest.common.valueobject.ExpertVal;



/**
 *
 * @author admin
 */
public class ExpertDao {

      /** Creates a new instance of ExpertDao */
    public ExpertDao() {
    }

   

    public boolean findExpertByPrk(Response rp, String expertid){
        boolean result = true;
        ArrayList al = new ArrayList();

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select * from  T_Expert where  ExpertID = ?";
        /* sql string end */

        /*
           get all of value of the production.
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            pstmt.setString(1, expertid);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                 al.add (new ExpertVal(
                                     rs.getString("ExpertID"),
                                     rs.getString ("UnitID"),
                                     rs.getString("Item_180"),
                                     rs.getString("Item_181"),
                                     rs.getString("Item_182"),
                                     rs.getString("Item_183"),
                                     rs.getString("Item_184"),
                                     rs.getString("WorkSpeciality"),
                                     rs.getString("Item_15"),
                                     rs.getString("GraduateSchool"),
                                     rs.getString("Speciality"),
                                     rs.getString("Degree"),
                                     rs.getString("Clan"),
                                     rs.getString("Nationality"),
                                     rs.getString("Duty"),
                                     rs.getString("Item_dqdm"),
                                     rs.getString("Item_185"),
                                     rs.getString("Item_37"),
                                     rs.getString("Item_38"),
                                     rs.getString("Item_39"),
                                     rs.getString("Item_40"),
                                     rs.getString("Email"),
                                     rs.getString("Photo"),
                                     rs.getString("ExpIntro"),
                                     rs.getString("Achievement"),
                                     rs.getString("StrongTrait"),
                                     rs.getString("NewEnergyClass1"),
                                     rs.getString("NewEnergyClass2"),
                                     rs.getString("TopicWords"),
                                     rs.getString("UserID"),
                                     rs.getString("IfNew"),
                                     rs.getString("IfRecomm"),
                                     rs.getString("ExpIntro_s"),
                                     rs.getString("Item_62"),
                                     rs.getString("WorkSpeciality_S"),
                                     rs.getString("ChannelShow"),
                                     rs.getString("HomepageShow")
                                    ));
            }
            rp.setValObjs (al);
            return result;
        }
        catch(SQLException e){
            System.out.println ("When get expert info ," +
                    " there is a exception occured!! ");
            System.out.println ("The class is ExpertDao! " +
                                 "The function is findExpertByPrk! " +
                                 e);
            result = false;
            return result;
        }
        catch(Exception e){
            System.out.println ("When call the getConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is ExpertDao and the " +
                                 "function is findExpertByPrk! " + e);
            result = false;
            return result;
        }
        finally{
            /* close the prestatement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao " +
                            "and the function is findExpertByPrk, when" +
                            " close the pstmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao and " +
                            "the function is findExpertByPrk, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ExpertDao and" +
                            "the function is findExpertByPrk, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

    public long countExpertByCondition(String condtion, Response rp) {

        ArrayList al = new ArrayList();
        long total = 0;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String sql = "select count(1) as cnt from  T_Expert where  " + condtion;
        /* sql string end */

       
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                total = rs.getLong("cnt");
            }
            return total;
        }
        catch(SQLException e){
            System.out.println("When countExpertByCondition, have a exception!" +
                                " The class name is ExpertDao." + e);
            return 0;
        }
        catch(Exception e){
            System.out.println ("When call the countExpertByCondition, have a"+
                                 "exception occured!   ");
            e.printStackTrace();

            return 0;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ExpertDao and" +
                            " the function is countExpertByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }
     
   

       public boolean findExpertByCondition(Response rp,String condtion,
                                                                int pageNo,
                                                                int pageRecord,
                                                                int total) {

        boolean result = true;
     //   boolean firsthalf = true;
        ArrayList al = new ArrayList();
        int iPrePageCount ;
        int tempRow = 0;
        Tools tool = new Tools();

        /* number of record in page */
        
        //在这里设置每页显示信息条数
        pageRecord = 5;
        
        
        int startRow = (pageNo -1) * pageRecord;
        int endRow = pageNo * pageRecord;

        if(endRow > total)
            endRow = total;

       
        tempRow = 1;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top " + Integer.toString(endRow);
        String sql = "  ExpertID,Item_180,WorkSpeciality,Item_185,ExpIntro,Photo from  T_Expert where  " + condtion;

      
            sql += " order by Item_180 ";
        /* sql string end */

        /*
           count plan production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                if(tempRow > startRow) {
                 ExpertVal expertVal = new ExpertVal();
                 expertVal.setExpertID(rs.getString("ExpertID"));
                 expertVal.setItem_180(rs.getString("Item_180"));
                 expertVal.setWorkSpeciality(rs.getString("WorkSpeciality"));
                 expertVal.setItem_185(rs.getString("Item_185"));
                 expertVal.setExpIntro(rs.getString("ExpIntro"));
                 expertVal.setPhoto(rs.getString("Photo"));
                 al.add (expertVal);
                }
                   
                else
                    tempRow++;
            }

           
                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When get the expert info, have a exception!" +
                                " The class name is ExpertDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getMyConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is ExpertDao and the " +
                                 "function is findExpertByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
               
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ExpertDao and" +
                            " the function is findExpertByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }


     public boolean findTopExpertByCondition(Response rp,String num,String con,String sorttype) {

        boolean result = true;
        ArrayList al = new ArrayList();
        Tools tool = new Tools();



        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top  " + num;
        String sql = " ExpertID,Item_180,WorkSpeciality from T_Expert where " + con ;
        if (sorttype.equals("r"))
              sql += " order by ChannelShow desc,Item_180 ";
        else
              sql += " order by Item_180 ";
        
        /* sql string end */


        try{

            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                 ExpertVal ExpertVal = new ExpertVal();
                 ExpertVal.setExpertID(rs.getString("ExpertID"));
                 ExpertVal.setItem_180(rs.getString("Item_180"));
                 ExpertVal.setWorkSpeciality(rs.getString("WorkSpeciality"));
                 al.add (ExpertVal);
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When get the expert info, have a exception!" +
                                " The class name is ExpertDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is ExpertDao and the " +
                                 "function is findTopExpertByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{

                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ExpertDao and" +
                            " the function is findTopExpertByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }

      public boolean setChannelShow(String expertid,String ifshow){
    	boolean result = true;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();

        String sql = "";
        try{
                   sql = " update  T_Expert set ";
                   sql += "ChannelShow = '"+ ifshow +"' ";
                  sql += "where ExpertID = '" + expertid + "'";
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (sql);
            pstmt.execute();
            return result;
        }
        catch(SQLException e){
            e.printStackTrace();
            result = false;
            return result;
        }
        catch(Exception e){
            e.printStackTrace();
            result = false;
            return result;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{
                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                	e.printStackTrace();
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                	e.printStackTrace();
                }
            }
        }
    }


   public boolean SetChannelShow_Batch(String ifshow,String[] id) {
        boolean flag = false;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();


        String sql = "update T_Expert set ChannelShow='"+ifshow +"' where ExpertID=?";

       try {
            conn = db.getSysConnectionPool ();
            pstmt = conn.prepareStatement(sql);
            for (int i = 0; i < id.length; i++) {
                pstmt.setString(1, id[i]);
                // ʹ������
                pstmt.addBatch();
             }
            // ִ������
           int[] result = pstmt.executeBatch();
           if (result[0] > 0) {
                flag = true;

            }
                return flag;
            }
            catch(SQLException e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            catch(Exception e){
                e.printStackTrace();
                flag = false;
                return flag;
            }
            finally {
                if(null != pstmt){
                    try{
                        pstmt.close ();
                        pstmt = null;
                    }
                    catch(SQLException e){
                        e.printStackTrace();
                     }
                    }

                     /* release the conn */
                     if(null != conn){
                     try{
                        conn.close();
                        conn = null;
                         db = null;
                        }
                        catch(Exception e){
                            e.printStackTrace();
                        }
                    }
                }


 }


      public boolean findExpertShowByCondition(Response rp,String condtion,
                                                                int pageNo,
                                                                int pageRecord,
                                                                int total) {

        boolean result = true;
     //   boolean firsthalf = true;
        ArrayList al = new ArrayList();
        int iPrePageCount ;
        int tempRow = 0;
        Tools tool = new Tools();

        /* number of record in page */
        int startRow = (pageNo -1) * pageRecord;
        int endRow = pageNo * pageRecord;

        if(endRow > total)
            endRow = total;


        tempRow = 1;

        /* dao object begin */
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Connection conn = null;
        DataBase db = new DataBase();
        /* dao object end */

        /* sql string begin */
        String rowcount = "select top " + Integer.toString(endRow);
        String sql = "  ExpertID,Item_180,WorkSpeciality,Item_185,ChannelShow,HomepageShow from  T_Expert where  " + condtion;


            sql += " order by Item_180 ";
        /* sql string end */

        /*
           count plan production then encapsulat as the entity bean
           and save them into ArrayList
         */
        try{
            conn = db.getSysConnectionPool();
            pstmt = conn.prepareStatement (rowcount + sql);
            rs = pstmt.executeQuery ();
            while(rs.next ()){
                if(tempRow > startRow) {
                 ExpertVal expertVal = new ExpertVal();
                 expertVal.setExpertID(rs.getString("ExpertID"));
                 expertVal.setItem_180(rs.getString("Item_180"));
                 expertVal.setWorkSpeciality(rs.getString("WorkSpeciality"));
                 expertVal.setItem_185(rs.getString("Item_185"));
                 expertVal.setChannelShow(rs.getString("ChannelShow"));
                 expertVal.setHomepageShow(rs.getString("HomepageShow"));
                 al.add (expertVal);
                }

                else
                    tempRow++;
            }


                rp.setValObjs (al);

            return true;
        }
        catch(SQLException e){
            System.out.println("When get the expert info, have a exception!" +
                                " The class name is ExpertDao." + e);
            return false;
        }
        catch(Exception e){
            System.out.println ("When call the getMyConnection, have a"+
                                 "exception occured!   ");
            System.out.println ("The class is ExpertDao and the " +
                                 "function is findExpertShowByCondition! " + e);

            return false;
        }
        finally{
            /* close the statement */
            if(null != pstmt){
                try{

                    pstmt.close ();
                    pstmt = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao, when" +
                            " close the stmp, happen a new exception! " + e);
                }
            }

            /* close the rs */
            if(null != rs){
                try{
                    rs.close ();
                    rs = null;
                }
                catch(SQLException e){
                    System.out.println ("In the class of ExpertDao, when" +
                            " close the rs, happen a new exception! " + e);
                }
            }

            /* release the conn */
            if(null != conn){
                try{
                    conn.close();
                    conn = null;
                    db = null;
                }
                catch(Exception e){
                    System.out.println ("In the class of ExpertDao and" +
                            " the function is findExpertShowByCondition, when " +
                            " close the conn, happen a new exception! " + e);
                }
            }
        }
    }


    

}
